package com.ugoodtech.mdcc.client.Utils;


import com.ugoodtech.mdcc.client.exception.BizException;
import com.ugoodtech.mdcc.client.exception.EnumBizError;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import lombok.Data;

/**
 * Created by Lance on 2019-08-08.
 */

/**
 * Excel工具类
 */
@Data
public class ExcelUtils {

	/**
	 * 将list数据转换成excel对象
	 *
	 * @param headTitleList 标题列表
	 * @param valueList 数据列表
	 * @return Excel对象
	 */
	public static Workbook generateExcel(List<String> headTitleList, List<List<String>> valueList) {
		return generateExcel(headTitleList, valueList, null, null);
	}

	/**
	 * 将list数据转换成excel对象
	 *
	 * @param headTitleList 标题列表
	 * @param valueList 数据列表
	 * @return Excel对象
	 */
	public static Workbook generateExcel(List<String> headTitleList, List<List<String>> valueList,
			CellStyle headingStyle, List<CellStyle> dataStyles) {
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet1 = workbook.createSheet();
		// 生成标题行
		Row row1 = sheet1.createRow(0);
		int lastHeadColumn = headTitleList.size();
		for (int i = 0; i < lastHeadColumn; i++) {
			Cell cell = row1.createCell(i);
			if (Objects.nonNull(headingStyle)) {
				cell.setCellStyle(headingStyle);
			}
			cell.setCellValue(headTitleList.get(i));
		}
		// 生成相应内容
		int valueListSize = valueList.size();
		for (int i = 0; i < valueListSize; i++) {
			Row valueRow = sheet1.createRow(i + 1);
			CellStyle cellStyle = dataStyles.get(i % dataStyles.size());
			List<String> eachRowValueList = valueList.get(i);
			int lastValueColumn = eachRowValueList.size();
			for (int j = 0; j < lastValueColumn; j++) {
				Cell valueCell = valueRow.createCell(j);
				valueCell.setCellStyle(cellStyle);
				valueCell.setCellValue(eachRowValueList.get(j));
			}
		}
		return workbook;
	}

	/**
	 * 将list数据转换成excel对象
	 *
	 * @param headTitleList 标题列表
	 * @param valueList 数据列表
	 * @return Excel对象
	 */
	public static void generateExcel(Sheet sheet1, List<String> headTitleList, List<List<String>> valueList,
			CellStyle headingStyle, List<CellStyle> dataStyles) {
		// 生成标题行
		Row row1 = sheet1.createRow(0);
		int lastHeadColumn = headTitleList.size();
		for (int i = 0; i < lastHeadColumn; i++) {
			Cell cell = row1.createCell(i);
			if (Objects.nonNull(headingStyle)) {
				cell.setCellStyle(headingStyle);
			}
			cell.setCellValue(headTitleList.get(i));
		}
		// 生成相应内容
		int valueListSize = valueList.size();
		for (int i = 0; i < valueListSize; i++) {
			Row valueRow = sheet1.createRow(i + 1);
			CellStyle cellStyle = null;
			if (dataStyles != null && dataStyles.size() != 0) {
				cellStyle = dataStyles.get(i % dataStyles.size());
			}
			List<String> eachRowValueList = valueList.get(i);
			int lastValueColumn = eachRowValueList.size();
			for (int j = 0; j < lastValueColumn; j++) {
				Cell valueCell = valueRow.createCell(j);
				if (dataStyles != null && dataStyles.size() != 0) {
					valueCell.setCellStyle(cellStyle);
				}
				valueCell.setCellValue(eachRowValueList.get(j));
			}
		}
	}

	public static Sheet generateSheet(Workbook workbook, String sheetName, List<String> headTitleList,
			List<List<String>> valueList) {
		Sheet sheet1 = workbook.createSheet(sheetName);
		// 生成标题行
		Row row1 = sheet1.createRow(0);
		int lastHeadColumn = headTitleList.size();
		for (int i = 0; i < lastHeadColumn; i++) {
			Cell cell = row1.createCell(i);
			cell.setCellValue(headTitleList.get(i));
		}
		// 生成相应内容
		int valueListSize = valueList.size();
		for (int i = 0; i < valueListSize; i++) {
			Row valueRow = sheet1.createRow(i + 1);
			List<String> eachRowValueList = valueList.get(i);
			int lastValueColumn = eachRowValueList.size();
			for (int j = 0; j < lastValueColumn; j++) {
				Cell valueCell = valueRow.createCell(j);
				valueCell.setCellValue(eachRowValueList.get(j));
			}
		}
		return sheet1;
	}

	/**
	 * 将对象列表转换成Excel
	 *
	 * @param headTitleList 标题列表
	 * @param dataList 数据列表
	 * @return 返回Excel对象
	 * @throws IllegalAccessException 转换错误
	 * @throws InvocationTargetException 转换错误
	 */
	public static <T> Workbook generateExcelWithObjectList(List<String> headTitleList,
			List<T> dataList)
			throws IllegalAccessException, InvocationTargetException, NoSuchMethodException {
		List<List<String>> dataValueList = new ArrayList<>();
		// 取出T的数据
		if (dataList == null || dataList.size() == 0) {
			return generateExcel(headTitleList, dataValueList);
		}
		T object = dataList.get(0);
		Map<String, String> fieldMap = new LinkedHashMap<>();
		Field[] fields = object.getClass().getDeclaredFields();
		for (Field field : fields) {
			fieldMap.put(field.getName(), "");
		}
		for (T object1 : dataList) {
			Map<String, String> dataMap = BeanUtils.describe(object1);
			List<String> valueList = new ArrayList<>();
			for (String key : fieldMap.keySet()) {
				valueList.add(dataMap.get(key));
			}
			dataValueList.add(valueList);
		}
		return generateExcel(headTitleList, dataValueList);
	}

	/**
	 * 解析Excel文件
	 *
	 * @param file 文件
	 * @return 文件内容
	 * @throws IOException 文件读取错误时的IO错误
	 * @throws InvalidFormatException Excel文件格式错误
	 */
	public static List<List<String>> resolveExcel(File file)
			throws IOException, InvalidFormatException {
		if (file.getName().endsWith("xls")) {
			try (InputStream stream = new FileInputStream(file)) {
				return resolveExcel(new HSSFWorkbook(stream));
			}
		}
		if (file.getName().endsWith("xlsx")) {
			return resolveExcel(new XSSFWorkbook(file));
		}
		return Arrays.asList();
	}

	/**
	 * 解析Workbook对象为List列表
	 *
	 * @param workbook Excel的workbook对象
	 * @return文件内容
	 */
	public static List<List<String>> resolveExcel(Workbook workbook) {
		Sheet sheet = workbook.getSheetAt(0);
		if (Objects.isNull(sheet)) {
			throw new BizException(EnumBizError.OPERATION_FAIL.setErrMsg("请提交正确格式的文件"));
		}
		int rowStartAt = sheet.getFirstRowNum();
		int currentRow = rowStartAt;
		int rowEndAt = sheet.getLastRowNum();
		Row row;
		List<List<String>> valueList = new ArrayList<>();
		for (; currentRow <= rowEndAt; currentRow++) {
			List<String> rowValueList = new ArrayList<>();
			row = sheet.getRow(currentRow);
			// 将该行内容转换为String列表
			int rowLength = row.getLastCellNum();
			for (int i = 0; i < rowLength; i++) {
				rowValueList.add(convertToString(row.getCell(i)));
			}
			valueList.add(rowValueList);
		}
		return valueList;
	}

	public static String convertToString(Cell cell) {
		if (Objects.isNull(cell)) {
			return "";
		}
		switch (cell.getCellTypeEnum()) {
			case _NONE:
			case BLANK:
				return "";
			case NUMERIC:
				return String.valueOf(new BigDecimal(cell.getNumericCellValue())).trim();
			case BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue()).trim();
			case STRING:
				return cell.getStringCellValue().trim();
		}
		throw new BizException(EnumBizError.OPERATION_FAIL.setErrMsg("数据解析错误"));
	}


	public static Cell getCell(Sheet sheet, Integer columnNum, Integer rowNum) {
		Row row = sheet.getRow(rowNum);
		return row.getCell(columnNum);
	}

	public static CellStyle getCellStyle(XSSFWorkbook workbook, Integer red, Integer green,
			Integer blue, Font font, Boolean setBoarder) {
		XSSFCellStyle cellStyle = workbook.createCellStyle();
		XSSFColor color = new XSSFColor(new java.awt.Color(red, green, blue));
		cellStyle.setFillForegroundColor(color);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setWrapText(true);
		if (setBoarder) {
			cellStyle.setBorderLeft(BorderStyle.THIN);
			cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
			cellStyle.setBorderRight(BorderStyle.THIN);
			cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
			cellStyle.setBorderTop(BorderStyle.THIN);
			cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
			cellStyle.setBorderBottom(BorderStyle.THIN);
			cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
		}
		if (Objects.nonNull(font)) {
			cellStyle.setFont(font);
		}
		return cellStyle;
	}


	public static CellStyle getCellStyle(XSSFWorkbook workbook, Integer red, Integer green,
			Integer blue, Font font) {
		return getCellStyle(workbook, red, green, blue, font, false);
	}


}
